﻿//------------------------------------------------------------------------------
// <copyright file="ProgramController.cs" company="David Martos">
//     Copyright (c) David Martos.  All rights reserved.
// </copyright>
// <summary>Program controller class. It contains all the logic related to this entity.</summary>
//  ----------------------------------------------------------------------------
namespace Dissemination.MVC.Controllers
{
    #region Using statements

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web.Mvc;

    using Extensions;

    using Repositories;
    using Models;
    using Dissemination.MVC.ViewModels;


    #endregion

    /// <summary>
    /// Program controller class. It contains all the logic related to this entity.
    /// </summary>
    public class ProgramController : ControllerBase
    {
        #region Repository
        /// <summary>
        /// Entity related repository. It contains all the database access functionalities.
        /// </summary>
        protected ProgramRepository Repository = new ProgramRepository();

        #endregion

        #region Index

        //
        // GET: /Program/

        /// <summary>
        /// Returns the view associated to the list of entities in the data source.
        /// </summary>
        /// <returns>Index view</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult Index()
        {
            if (log.IsDebugEnabled) log.Debug("Entering Programs list...");
            var model = Repository.List();
            if (log.IsDebugEnabled) log.Debug("Leaving Programs list...");
            return View(model);
        }

        #endregion

        #region Details

        //
        // GET: /Program/Details/5

        /// <summary>
        /// Returns the view associated to the details of an entity in the data source.
        /// </summary>
        /// <param name="id">The entity id.</param>
        /// <returns>Details view</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult Details(int id)
        {
            if (log.IsDebugEnabled) log.DebugFormat("Entering Program details with id={0}...", id);
            ProgramViewModel viewModel = new ProgramViewModel
            {
                Program = Repository.Get(id),
                SortedTargetAreas = Repository.GetTargetAreasByProgramID(id)
            };
            
            if (viewModel.Program == null)
            {
                if (log.IsErrorEnabled) log.ErrorFormat("Program with id={0} not found", id);
                return View("NotFound");
            }

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program details with id={0}...", id);
            return View(viewModel);
        }

        #endregion

        #region New

        //
        // GET: /Program/Create

        /// <summary>
        /// Returns the view associated with an empty entity
        /// </summary>
        /// <returns>Create view</returns>
        [Authorize(Roles = "Administrator")]
        public ActionResult Create()
        {
            if (log.IsDebugEnabled) log.Debug("Entering Program creation (GET)...");
            if (log.IsDebugEnabled) log.Debug("Leaving Program creation (GET)...");
            return View(new Program());
        }

        //
        // POST: /Program/Create

        /// <summary>
        /// Creates the specified entity.
        /// </summary>
        /// <param name="program">The entity</param>
        /// <returns>Details view</returns>
        [HttpPost]
        [Authorize(Roles = "Administrator")]
        public ActionResult Create(Program program)
        {
            if (log.IsDebugEnabled) log.Debug("Entering Program creation (POST)...");
            if (ModelState.IsValid)
            {
                if (program.ProgramIsDefault)
                {
                    Repository.ClearDefaultProgram(-1);
                }
                Repository.Add(program);
                Repository.Save();

                if (log.IsDebugEnabled) log.Debug("Leaving Program creation (POST)...");
                return RedirectToAction("Details", new { id = program.ProgramID });
            }

            if (log.IsDebugEnabled)
            {
                log.Debug("Model state not valid in Program creation (POST)...");
                log.DebugFormat("ProgramID: {0}", program.ProgramID);
                log.DebugFormat("ProgramIsDefault: {0}", program.ProgramIsDefault);
                log.DebugFormat("ProgramName: {0}", program.ProgramName);
            }

            return View(program);
        }

        #endregion

        #region Edit

        //
        // GET: /Program/Edit/5

        /// <summary>
        /// Returns the view associated with the edit of an existing entity.
        /// </summary>
        /// <param name="id">The id of the entity to edit</param>
        /// <returns>Edit view</returns>
        [Authorize(Roles = "Administrator")]
        public ActionResult Edit(int id)
        {
            if (log.IsDebugEnabled) log.DebugFormat("Entering Program with id {0} edit (GET)...", id);
            Program program = Repository.Get(id);
            
            if (program == null)
            {
                if (log.IsErrorEnabled) log.ErrorFormat("Program with id={0} not found", id);
                return View("NotFound");
            }

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} edit (GET)...", id);
            return View(program);
        }

        //
        // POST: /Program/Edit/5

        /// <summary>
        /// Edits the specified entity.
        /// </summary>
        /// <param name="id">The id of the existing entity.</param>
        /// <param name="collection">The collection of values entered.</param>
        /// <returns>Details view</returns>
        [HttpPost]
        [Authorize(Roles = "Administrator")]
        public ActionResult Edit(int id, FormCollection collection)
        {
            if (log.IsDebugEnabled) log.DebugFormat("Entering Program with id {0} edit (POST)...", id);
            Program program = Repository.Get(id);
            
            try
            {
                UpdateModel(program);
                if (program.ProgramIsDefault)
                {
                    if (log.IsDebugEnabled) log.DebugFormat("Clearing default Program. Leaving Program with id {0}", id);
                    Repository.ClearDefaultProgram(id);
                }
                Repository.Save();

                if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} edit (POST)...", id);
                return RedirectToAction("Details", new { id = program.ProgramID });
            }
            catch (Exception ex)
            {
                if (log.IsErrorEnabled) log.Error("Error editing Program", ex);
                //ModelState.AddRuleViolations(program.GetRuleViolations());
                return View(program);
            }
        }

        #endregion

        #region Delete

        //
        // GET: /Program/Delete/5

        /// <summary>
        /// Returns the view associated with a given entity to be deleted
        /// </summary>
        /// <param name="id">The id of the existing entity</param>
        /// <returns>Delete view</returns>
        [Authorize(Roles = "Administrator")]
        public ActionResult Delete(int id)
        {
            throw new NotImplementedException();

            if (log.IsDebugEnabled) log.DebugFormat("Entering Program with id {0} delete (GET)...", id);
            Program program = Repository.Get(id);

            if (program == null)
            {
                if (log.IsErrorEnabled) log.ErrorFormat("Program with id={0} not found", id);
                return View("NotFound");
            }

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} delete (GET)...", id);
            return View(program);
        }

        // POST: /Program/Delete/5

        /// <summary>
        /// Deletes the specified entity.
        /// </summary>
        /// <param name="id">The id of the existing entity</param>
        /// <param name="confirmButton">The confirm button.</param>
        /// <returns>Deleted view</returns>
        [HttpPost]
        [Authorize(Roles = "Administrator")]
        public ActionResult Delete(int id, string confirmButton)
        {
            throw new NotImplementedException();

            if (log.IsDebugEnabled) log.DebugFormat("Entering Program with id {0} delete (POST)...", id);
            Program program = Repository.Get(id);

            if (program == null)
            {
                if (log.IsErrorEnabled) log.ErrorFormat("Program with id={0} not found", id);
                return View("NotFound");
            }

            Repository.Delete(program);
            Repository.Save();

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} delete (POST)...", id);
            return View("Deleted");
        }

        #endregion

        // GET: /Program/Survey/5

        /// <summary>
        /// Returns the survey report
        /// </summary>
        /// <param name="id">Program id</param>
        /// <returns>Survey report</returns>
        [Authorize(Roles = "Administrator")]
        public ActionResult Survey(int id)
        {
            return View("NotImplemented");
        }

        // GET: /Program/Results/5

        /// <summary>
        /// Returns the results report
        /// </summary>
        /// <param name="id">Program id</param>
        /// <returns>Results report</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult Results(int id)
        {
            var viewModel = new ProgramViewModel
            {
                Program = Repository.Get(id),
                SortedTargetAreas = Repository.GetTargetAreasByProgramID(id)
            };

            var program = viewModel.Program;

            List<ExcelCellChange> excelChanges = new List<ExcelCellChange>();
            List<ExcelSheetInformation> sheets = new List<ExcelSheetInformation>();

            excelChanges.Add(new ExcelCellChange { Row = 2, Column = 2, Value = program.ProgramName });

            int row = 7;
            foreach (TargetArea ta in program.TargetAreas)
            {
                foreach (SurveyResult sr in Repository.GetSurveyResultsByTargetAreaID(ta.TargetAreaID))
                {
                    if (sr.SurveyResultsFilled)
                    {
                        excelChanges.AddRange(new List<ExcelCellChange>
                              {
                                      new ExcelCellChange { Row = row, Column = 2, Value = ta.Country.CountryName },
                                      new ExcelCellChange { Row = row, Column = 3, Value = ta.TargetAreaCode },
                                      new ExcelCellChange { Row = row, Column = 4, Value = sr.Survey.SurveyOrdinal },
                                      new ExcelCellChange { Row = row, Column = 5, Value = sr.Survey.SurveyPeriod },

                                      new ExcelCellChange { Row = row, Column = 7, Value = sr.SurveyResultsPopulation },
                                      new ExcelCellChange { Row = row, Column = 8, Value = sr.SurveyResultsHospitals },
                                      new ExcelCellChange { Row = row, Column = 9, Value = sr.SurveyResultsBeds },
                                      new ExcelCellChange { Row = row, Column = 10, Value = sr.SurveyResultsDeath },
                                      new ExcelCellChange { Row = row, Column = 11, Value = sr.SurveyResultsBedsICU },
                                      new ExcelCellChange { Row = row, Column = 12, Value = sr.SurveyResultsNeurosurgery },

                                      new ExcelCellChange { Row = row, Column = 14, Value = sr.SurveyResultsAdmittedICU },
                                      new ExcelCellChange { Row = row, Column = 15, Value = sr.SurveyResultsDeathICU },
                                      new ExcelCellChange { Row = row, Column = 16, Value = sr.SurveyResultsPotentialDonors },
                                      new ExcelCellChange { Row = row, Column = 17, Value = sr.SurveyResultsEligibleDonors},

                                      new ExcelCellChange { Row = row, Column = 19, Value = sr.SurveyResultsActualDonors },
                                      new ExcelCellChange { Row = row, Column = 20, Value = sr.SurveyResultsUtilizedDonors },
                                      new ExcelCellChange { Row = row, Column = 21, Value = sr.SurveyResultsProcuredOrgans },
                                      new ExcelCellChange { Row = row, Column = 22, Value = sr.SurveyResultsCadaveric },
                                      new ExcelCellChange { Row = row, Column = 23, Value = sr.SurveyResultsDonorCoordinatorsFull },
                                      new ExcelCellChange { Row = row, Column = 24, Value = sr.SurveyResultsDonorCoordinatorsPart },
                              });
                        row++;
                    }
                }
            }

            ExcelSheetInformation sheet = new ExcelSheetInformation() { Name = "Surveys", Changes = excelChanges };
            sheets.Add(sheet);

            var changes = sheets.AsQueryable();
            string temporaryFileName = Server.MapPath(@"~/temp/" + Guid.NewGuid() + ".xlsx");

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            return this.Excel("survey_results_report.xlsx", temporaryFileName, Server.MapPath(@"~/templates/ProgramSurveyResults.xlsx"), "Surveys", changes);
        }

        /// <summary>
        /// Returns the results report
        /// </summary>
        /// <param name="id">Program id</param>
        /// <returns>Results report</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult TestResults(int id)
        {
            var programViewModel = new ProgramViewModel
            {
                Program = Repository.Get(id),
                SortedTargetAreas = Repository.GetTargetAreasByProgramID(id)
            };

            List<ExcelCellChange> excelFistPageChanges = new List<ExcelCellChange>();
            List<ExcelSheetInformation> sheets = new List<ExcelSheetInformation>();

            excelFistPageChanges.Add(new ExcelCellChange { Row = 2, Column = 2, Value = programViewModel.Program.ProgramName });

            int row = 8;

            foreach (TargetArea ta in programViewModel.SortedTargetAreas)
            {
                var seminars = Repository.GetSeminarsByTargetAreaID(ta.TargetAreaID);

                foreach (Seminar s in seminars)
                {
                    ExcelSheetInformation sheet = new ExcelSheetInformation() { Name = string.Format("{0}-{1}", ta.TargetAreaCode, s.SeminarOrdinal), Template = "SP1_1", Changes = new List<ExcelCellChange>() };

                    sheet.Changes.AddRange(new List<ExcelCellChange>
                    {
                        new ExcelCellChange { Row = 2, Column = 2, Value = ta.Program.ProgramName },
                        new ExcelCellChange { Row = 3, Column = 2, Value = string.Format("EOD Seminar {0} - {1} ({2}) · {3}", s.SeminarOrdinal, ta.TargetAreaCode, ta.Country.CountryName, s.FriendlyDate) },
                        new ExcelCellChange { Row = 5, Column = 3, Value = s.TestResults.Count },
                        new ExcelCellChange { Row = 7, Column = 3, Value = s.TestResults.Sum(ts => ts.TestResultCorrectAnswers) },
                        new ExcelCellChange { Row = 8, Column = 3, Value = s.TestResults.Sum(ts => ts.TestResultIncorrectAnswers) },
                        new ExcelCellChange { Row = 9, Column = 3, Value = s.TestResults.Sum(ts => ts.TestResultNotAnswered) },
                    });
                    sheets.Add(sheet);
                }

                excelFistPageChanges.AddRange(new List<ExcelCellChange>
                {
                    new ExcelCellChange { Row = row, Column = 2, Value = ta.Country.CountryName },
                    new ExcelCellChange { Row = row, Column = 3, Value = ta.TargetAreaCode },
                    new ExcelCellChange { Row = row, Column = 5, Value = ta.Seminars.Sum(s => s.TestResults.Count) },
                    new ExcelCellChange { Row = row, Column = 7, Value = ta.Seminars.Sum(s => s.TestResults.Sum(tr => tr.TestResultCorrectAnswers)) },
                    new ExcelCellChange { Row = row, Column = 8, Value = ta.Seminars.Sum(s => s.TestResults.Sum(tr => tr.TestResultIncorrectAnswers)) },
                    new ExcelCellChange { Row = row, Column = 9, Value = ta.Seminars.Sum(s => s.TestResults.Sum(tr => tr.TestResultNotAnswered)) },
                });

                row++;
            }

            excelFistPageChanges.AddRange(new List<ExcelCellChange>
            {
                new ExcelCellChange { Row = 5, Column = 5, Value = programViewModel.Program.TargetAreas.Sum(ta => ta.Seminars.Sum(s => s.TestResults.Count)) },
                new ExcelCellChange { Row = 5, Column = 7, Value = programViewModel.Program.TargetAreas.Sum(ta => ta.Seminars.Sum(s => s.TestResults.Sum(tr => tr.TestResultCorrectAnswers))) },
                new ExcelCellChange { Row = 5, Column = 8, Value = programViewModel.Program.TargetAreas.Sum(ta => ta.Seminars.Sum(s => s.TestResults.Sum(tr => tr.TestResultIncorrectAnswers))) },
                new ExcelCellChange { Row = 5, Column = 9, Value = programViewModel.Program.TargetAreas.Sum(ta => ta.Seminars.Sum(s => s.TestResults.Sum(tr => tr.TestResultNotAnswered))) },
            });

            ExcelSheetInformation firstSheet = new ExcelSheetInformation
            {
                Name = "EODs Test Results SUMMARY",
                Changes = excelFistPageChanges
            };

            sheets.Add(firstSheet);

            string temporaryFileName = Server.MapPath(@"~/temp/" + Guid.NewGuid() + ".xlsx");

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            return this.Excel("survey_results_report.xlsx", temporaryFileName, Server.MapPath(@"~/templates/EOD_Seminars_Tests_Results.xlsx"), "EODs Test Results SUMMARY", sheets.AsQueryable());
        }

        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult Assessments(int id)
        {
            var programViewModel = new ProgramViewModel
            {
                Program = Repository.Get(id),
                SortedTargetAreas = Repository.GetTargetAreasByProgramID(id)
            };

            List<ExcelCellChange> excelFistPageChanges = new List<ExcelCellChange>();
            List<ExcelSheetInformation> sheets = new List<ExcelSheetInformation>();

            excelFistPageChanges.Add(new ExcelCellChange { Row = 2, Column = 2, Value = programViewModel.Program.ProgramName });

            int row = 9;

            foreach (TargetArea ta in programViewModel.SortedTargetAreas)
            {

                var seminars = Repository.GetSeminarsByTargetAreaID(ta.TargetAreaID);
                var assessmentOrganDonorPerSeminarAcum = new List<double?>();

                foreach (Seminar s in seminars)
                {
                    ExcelSheetInformation sheet = new ExcelSheetInformation() { Name = string.Format("{0}-{1}", ta.TargetAreaCode, s.SeminarOrdinal), Template = "SeminarTemplate", Changes = new List<ExcelCellChange>() };

                    var assessmentOrganDonor = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentOrganDonorP), s.Assessments.Average(a => a.AssessmentOrganDonorC), s.Assessments.Average(a => a.AssessmentOrganDonorQ));
                    var assessmentCaseStudies = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentCaseStudiesP), s.Assessments.Average(a => a.AssessmentCaseStudiesC), s.Assessments.Average(a => a.AssessmentCaseStudiesQ));
                    var assessmentBrainDeath = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentBrainDeathP), s.Assessments.Average(a => a.AssessmentBrainDeathC), s.Assessments.Average(a => a.AssessmentBrainDeathQ));
                    var assessmentSimulation = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentSimulationP), s.Assessments.Average(a => a.AssessmentSimulationC), s.Assessments.Average(a => a.AssessmentSimulationQ));
                    var assessmentDonor = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentDonorP), s.Assessments.Average(a => a.AssessmentDonorC), s.Assessments.Average(a => a.AssessmentDonorQ));
                    var assessmentApproach = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentApproachP), s.Assessments.Average(a => a.AssessmentApproachC), s.Assessments.Average(a => a.AssessmentApproachQ));
                    var assessmentInterviews = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentInterviewsP), s.Assessments.Average(a => a.AssessmentInterviewsC), s.Assessments.Average(a => a.AssessmentInterviewsQ));
                    var assessmentRetrieval = new AssessmentLecture(s.Assessments.Average(a => a.AssessmentRetrievalP), s.Assessments.Average(a => a.AssessmentRetrievalC), s.Assessments.Average(a => a.AssessmentRetrievalQ));

                    double? assessmentOrganDonorPPerSeminar = s.Assessments.Average(a => a.AssessmentOrganDonorP);
                    double? assessmentOrganDonorCPerSeminar = s.Assessments.Average(a => a.AssessmentOrganDonorC);
                    double? assessmentOrganDonorQPerSeminar = s.Assessments.Average(a => a.AssessmentOrganDonorQ);

                    var assessmentOrganDonorPerSeminar = new List<double?> { assessmentOrganDonorPPerSeminar, assessmentOrganDonorCPerSeminar, assessmentOrganDonorQPerSeminar };
                    assessmentOrganDonorPerSeminarAcum.Add(assessmentOrganDonorPerSeminar.Average()??0);

                    sheet.Changes.AddRange(new List<ExcelCellChange>
                              {
                                new ExcelCellChange { Row = 2, Column = 2, Value = ta.Program.ProgramName },
                                new ExcelCellChange { Row = 3, Column = 2, Value = string.Format("EOD Seminar {0} - {1} ({2}) · {3}", s.SeminarOrdinal, ta.TargetAreaCode, ta.Country.CountryName, s.FriendlyDate) },
                                new ExcelCellChange { Row = 5, Column = 3, Value = s.Assessments.Count },

                                new ExcelCellChange { Row = 9, Column = 3, Value = assessmentOrganDonor.Presentation },
                                new ExcelCellChange { Row = 9, Column = 4, Value = assessmentOrganDonor.Contents },
                                new ExcelCellChange { Row = 9, Column = 5, Value = assessmentOrganDonor.QuestionsAnswered },

                                new ExcelCellChange { Row = 10, Column = 3, Value = assessmentCaseStudies.Presentation },
                                new ExcelCellChange { Row = 10, Column = 4, Value = assessmentCaseStudies.Contents },
                                new ExcelCellChange { Row = 10, Column = 5, Value = assessmentCaseStudies.QuestionsAnswered },

                                new ExcelCellChange { Row = 11, Column = 3, Value = assessmentBrainDeath.Presentation },
                                new ExcelCellChange { Row = 11, Column = 4, Value = assessmentBrainDeath.Contents },
                                new ExcelCellChange { Row = 11, Column = 5, Value = assessmentBrainDeath.QuestionsAnswered },

                                new ExcelCellChange { Row = 12, Column = 3, Value = assessmentSimulation.Presentation },
                                new ExcelCellChange { Row = 12, Column = 4, Value = assessmentSimulation.Contents },
                                new ExcelCellChange { Row = 12, Column = 5, Value = assessmentSimulation.QuestionsAnswered },

                                new ExcelCellChange { Row = 13, Column = 3, Value = assessmentDonor.Presentation },
                                new ExcelCellChange { Row = 13, Column = 4, Value = assessmentDonor.Contents },
                                new ExcelCellChange { Row = 13, Column = 5, Value = assessmentDonor.QuestionsAnswered },

                                new ExcelCellChange { Row = 14, Column = 3, Value = assessmentApproach.Presentation },
                                new ExcelCellChange { Row = 14, Column = 4, Value = assessmentApproach.Contents },
                                new ExcelCellChange { Row = 14, Column = 5, Value = assessmentApproach.QuestionsAnswered },

                                new ExcelCellChange { Row = 15, Column = 3, Value = assessmentInterviews.Presentation },
                                new ExcelCellChange { Row = 15, Column = 4, Value = assessmentInterviews.Contents },
                                new ExcelCellChange { Row = 15, Column = 5, Value = assessmentInterviews.QuestionsAnswered },

                                new ExcelCellChange { Row = 16, Column = 3, Value = assessmentRetrieval.Presentation },
                                new ExcelCellChange { Row = 16, Column = 4, Value = assessmentRetrieval.Contents },
                                new ExcelCellChange { Row = 16, Column = 5, Value = assessmentRetrieval.QuestionsAnswered },

                                new ExcelCellChange { Row = 19, Column = 3, Value = s.Assessments.Average(a => a.AssessmentOrganization) },
                                new ExcelCellChange { Row = 20, Column = 3, Value = s.Assessments.Average(a => a.AssessmentTeaching) },
                                new ExcelCellChange { Row = 21, Column = 3, Value = s.Assessments.Average(a => a.AssessmentSupport) },
                                new ExcelCellChange { Row = 22, Column = 3, Value = s.Assessments.Average(a => a.AssessmentSecretariat) },

                                new ExcelCellChange { Row = 25, Column = 3, Value = s.Assessments.Average(a => a.AssessmentPreRegistration) },
                                new ExcelCellChange { Row = 26, Column = 3, Value = s.Assessments.Average(a => a.AssessmentPostRegistration) },

                                new ExcelCellChange { Row = 28, Column = 3, Value = s.Assessments.Average(a => a.AssessmentApplicability) },

                                new ExcelCellChange { Row = 30, Column = 3, Value = s.Assessments.Average(a => a.AssessmentOverall) },

                              });
                    sheets.Add(sheet);

                }

                excelFistPageChanges.AddRange(new List<ExcelCellChange>
                              {
                                      new ExcelCellChange { Row = row, Column = 2, Value = ta.Country.CountryName },
                                      new ExcelCellChange { Row = row, Column = 3, Value = ta.TargetAreaCode },
                                      
                                      new ExcelCellChange { Row = row, Column = 5, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentOrganDonorP))??0 },
                                      new ExcelCellChange { Row = row, Column = 6, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentOrganDonorC))??0 },
                                      new ExcelCellChange { Row = row, Column = 7, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentOrganDonorQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 9, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentCaseStudiesP))??0 },
                                      new ExcelCellChange { Row = row, Column = 10, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentCaseStudiesC))??0 },
                                      new ExcelCellChange { Row = row, Column = 11, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentCaseStudiesQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 13, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentBrainDeathP))??0 },
                                      new ExcelCellChange { Row = row, Column = 14, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentBrainDeathC))??0 },
                                      new ExcelCellChange { Row = row, Column = 15, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentBrainDeathQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 17, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentSimulationP))??0 },
                                      new ExcelCellChange { Row = row, Column = 18, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentSimulationC))??0 },
                                      new ExcelCellChange { Row = row, Column = 19, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentSimulationQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 21, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentDonorP))??0 },
                                      new ExcelCellChange { Row = row, Column = 22, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentDonorC))??0 },
                                      new ExcelCellChange { Row = row, Column = 23, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentDonorQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 25, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentApproachP))??0 },
                                      new ExcelCellChange { Row = row, Column = 26, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentApproachC))??0 },
                                      new ExcelCellChange { Row = row, Column = 27, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentApproachQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 29, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentInterviewsP))??0 },
                                      new ExcelCellChange { Row = row, Column = 30, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentInterviewsC))??0 },
                                      new ExcelCellChange { Row = row, Column = 31, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentInterviewsQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 33, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentRetrievalP))??0 },
                                      new ExcelCellChange { Row = row, Column = 34, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentRetrievalC))??0 },
                                      new ExcelCellChange { Row = row, Column = 35, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentRetrievalQ))??0 },

                                      new ExcelCellChange { Row = row, Column = 38, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentOrganization))??0 },
                                      new ExcelCellChange { Row = row, Column = 39, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentTeaching))??0 },
                                      new ExcelCellChange { Row = row, Column = 40, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentSupport))??0 },
                                      new ExcelCellChange { Row = row, Column = 41, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentSecretariat))??0 },

                                      new ExcelCellChange { Row = row, Column = 43, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentPreRegistration))??0 },
                                      new ExcelCellChange { Row = row, Column = 44, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentPostRegistration))??0 },
                                      new ExcelCellChange { Row = row, Column = 46, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentApplicability))??0 },
                                      new ExcelCellChange { Row = row, Column = 47, Value = ta.Seminars.Average(targetarea => targetarea.Assessments.Average(a => a.AssessmentOverall))??0 },

                              });

                row++;

            }

            ExcelSheetInformation firstSheet = new ExcelSheetInformation
            {
                Name = "EODs Assessments SUMMARY",
                Changes = excelFistPageChanges
            };

            sheets.Add(firstSheet);

            string temporaryFileName = Server.MapPath(@"~/temp/" + Guid.NewGuid() + ".xlsx");

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            return this.Excel("EOD_assessments_report.xlsx", temporaryFileName, Server.MapPath(@"~/templates/EOD_Seminars_Assessments.xlsx"), "EODs Assessments SUMMARY", sheets.AsQueryable());
        }

        // GET: /Program/Activity/5

        /// <summary>
        /// Returns the activity report
        /// </summary>
        /// <param name="id">Program id</param>
        /// <returns>Activity report</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult Activity(int id)
        {
            if (log.IsDebugEnabled) log.DebugFormat("Entering Program with id {0} activity...", id);

            var viewModel = Repository.GetSurveyActivityReport(id);

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} activity...", id);
            
            return View(viewModel);
        }

        // GET: /Program/Eod/5

        /// <summary>
        /// Returns the EOD report
        /// </summary>
        /// <param name="id">The program id</param>
        /// <returns>The EOD report</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult Eod(int id)
        {
            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} eod...", id);

            var viewModel = Repository.GetEODActivityReport(id);

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} eod...", id);
            
            return View(viewModel);
        }

        // GET: /Program/Ta/5

        /// <summary>
        /// Returns the TA report
        /// </summary>
        /// <param name="id">The program id</param>
        /// <returns>The TA report</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult Participants(int id)
        {
            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            Program program = Repository.Get(id);

            List<ExcelCellChange> excelChanges = new List<ExcelCellChange>();
            List<ExcelSheetInformation> sheets = new List<ExcelSheetInformation>();

            excelChanges.Add(new ExcelCellChange { Row = 2, Column = 2, Value = program.ProgramName });

            int row = 6;
            var seminars = Repository.GetSeminarsByProgramID(id);

            foreach (Seminar s in seminars)
            {
                foreach (Participant p in s.Participants)
                {
                    excelChanges.AddRange(new List<ExcelCellChange>
                              {
                                      new ExcelCellChange { Row = row, Column = 2, Value = s.SeminarCode },
                                      new ExcelCellChange { Row = row, Column = 3, Value = s.FriendlyDate },
                                      new ExcelCellChange { Row = row, Column = 4, Value = s.SeminarCity },
                                      new ExcelCellChange { Row = row, Column = 5, Value = s.SeminarCenter },
                                      new ExcelCellChange { Row = row, Column = 6, Value = s.TargetArea.Country.CountryName },
                                      new ExcelCellChange { Row = row, Column = 7, Value = s.TargetArea.TargetAreaCode },
                                      new ExcelCellChange { Row = row, Column = 9, Value = p.ParticipantName },
                                      new ExcelCellChange { Row = row, Column = 10, Value = p.ParticipantSurname },
                                      new ExcelCellChange { Row = row, Column = 11, Value = p.ParticipantEmail },
                                      new ExcelCellChange { Row = row, Column = 12, Value = p.Gender.GenderName },
                                      new ExcelCellChange { Row = row, Column = 13, Value = p.ParticipantAge.ToString() },
                                      new ExcelCellChange { Row = row, Column = 15, Value = p.ParticipantDepartment },
                                      new ExcelCellChange { Row = row, Column = 16, Value = p.ParticipantPosition },
                              });
                    row++;
                }
            }

            ExcelSheetInformation sheet = new ExcelSheetInformation() { Name = "Participants", Changes = excelChanges };
            sheets.Add(sheet);
            var changes = sheets.AsQueryable();
            string temporaryFileName = Server.MapPath(@"~/temp/" + Guid.NewGuid() + ".xlsx");

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            return this.Excel("EOD_participants.xlsx", temporaryFileName, Server.MapPath(@"~/templates/EOD_Participants_Programme.xlsx"), "Participants", changes);
        }

        /// <summary>
        /// Returns the TA report
        /// </summary>
        /// <param name="id">The program id</param>
        /// <returns>The TA report</returns>
        [Authorize(Roles = "Demostrator Administrator")]
        public ActionResult Ta(int id)
        {
            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            Program program = Repository.Get(id);

            List<ExcelCellChange> excelChanges = new List<ExcelCellChange>();
            List<ExcelSheetInformation> sheets = new List<ExcelSheetInformation>();

            excelChanges.Add(new ExcelCellChange { Row = 2, Column = 2, Value = program.ProgramName });

            int row = 6;
            foreach (TargetArea ta in program.TargetAreas)
            {
                foreach (Reporter r in Repository.GetReportersByTargetAreaID(ta.TargetAreaID))
                {
                    excelChanges.AddRange(new List<ExcelCellChange>
                              {
                                      new ExcelCellChange { Row = row, Column = 2, Value = ta.Country.CountryName },
                                      new ExcelCellChange { Row = row, Column = 3, Value = ta.TargetAreaCode },
                                      new ExcelCellChange { Row = row, Column = 5, Value = r.ReporterName },
                                      new ExcelCellChange { Row = row, Column = 6, Value = r.ReporterSurname },
                                      new ExcelCellChange { Row = row, Column = 7, Value = r.ReporterEmail },
                                      new ExcelCellChange { Row = row, Column = 8, Value = r.Gender.GenderName },
                                      new ExcelCellChange { Row = row, Column = 9, Value = r.ReporterAge.ToString() },
                                      new ExcelCellChange { Row = row, Column = 11, Value = r.ReporterYearsOfExperience.ToString() },
                                      new ExcelCellChange { Row = row, Column = 12, Value = r.ReporterDepartment },
                                      new ExcelCellChange { Row = row, Column = 13, Value = r.ReporterPosition },
                                      new ExcelCellChange { Row = row, Column = 14, Value = r.ReporterAcademicDegree },
                                      new ExcelCellChange { Row = row, Column = 15, Value = r.ReporterDevotedTime.ToString() },
                              });
                    row++;
                }
            }

            ExcelSheetInformation sheet = new ExcelSheetInformation() { Name = "Reporters", Changes = excelChanges };
            sheets.Add(sheet);
            var changes = sheets.AsQueryable();
            string temporaryFileName = Server.MapPath(@"~/temp/" + Guid.NewGuid() + ".xlsx");

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            return this.Excel("reporters_report.xlsx", temporaryFileName, Server.MapPath(@"~/templates/ProgramReporters.xlsx"), "Reporters", changes);
        }

        // GET: /Program/Evaluation/5

        /// <summary>
        /// Returns the evaluation report
        /// </summary>
        /// <param name="id">The program ID</param>
        /// <returns>The evaluation report</returns>
        [Authorize(Roles = "Administrator")]
        public ActionResult Evaluation(int id)
        {
            return View("NotImplemented");
        }

        // GET: /Program/FollowUp/5

        /// <summary>
        /// Returns the Follow Up report
        /// </summary>
        /// <param name="id">The program ID</param>
        /// <returns>The Follow Up report</returns>
        [Authorize(Roles = "Demostrator,Administrator")]
        public ActionResult FollowUps(int id)
        {
            var programViewModel = new ProgramViewModel
            {
                Program = Repository.Get(id),
                SortedTargetAreas = Repository.GetTargetAreasByProgramID(id)
            };

            List<ExcelCellChange> excelFistPageChanges = new List<ExcelCellChange>();
            List<ExcelSheetInformation> sheets = new List<ExcelSheetInformation>();

            excelFistPageChanges.Add(new ExcelCellChange { Row = 2, Column = 2, Value = programViewModel.Program.ProgramName });

            int row = 10;

            foreach (TargetArea ta in programViewModel.SortedTargetAreas)
            {
                var seminars = Repository.GetSeminarsByTargetAreaID(ta.TargetAreaID);

                #region Foreach seminar

                foreach (Seminar s in seminars)
                {
                    // New seminar sheet
                    ExcelSheetInformation sheet = new ExcelSheetInformation() { Name = string.Format("{0}-{1}", ta.TargetAreaCode, s.SeminarOrdinal), Template = "SP1_1", Changes = new List<ExcelCellChange>() };

                    sheet.Changes.AddRange(new List<ExcelCellChange>
                              {
                                new ExcelCellChange { Row = 2, Column = 2, Value = ta.Program.ProgramName },
                                new ExcelCellChange { Row = 3, Column = 2, Value = string.Format("EOD Seminar {0} - {1} ({2}) · {3}", s.SeminarOrdinal, ta.TargetAreaCode, ta.Country.CountryName, s.FriendlyDate) },
                                new ExcelCellChange { Row = 5, Column = 5, Value = s.FollowUps.Count },
                                new ExcelCellChange { Row = 8, Column = 3, Value = s.FollowUps.Average(fu => fu.FollowUpAge)??0 },
                                new ExcelCellChange { Row = 9, Column = 3, Value = s.FollowUps.Count(fu => fu.Gender.GenderName == "Male") },
                                new ExcelCellChange { Row = 10, Column = 3, Value = s.FollowUps.Count(fu => fu.Gender.GenderName == "Female") },
                                new ExcelCellChange { Row = 13, Column = 2, Value = s.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 1) },
                                new ExcelCellChange { Row = 14, Column = 2, Value = s.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 2) },
                                new ExcelCellChange { Row = 15, Column = 2, Value = s.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 3) },
                                new ExcelCellChange { Row = 16, Column = 2, Value = s.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 4) },
                                new ExcelCellChange { Row = 17, Column = 2, Value = s.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 5) },
                                new ExcelCellChange { Row = 18, Column = 2, Value = s.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 6) },
                                new ExcelCellChange { Row = 21, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 1) },
                                new ExcelCellChange { Row = 22, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 2) },
                                new ExcelCellChange { Row = 23, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 3) },
                                new ExcelCellChange { Row = 24, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 4) },
                                new ExcelCellChange { Row = 25, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 5) },
                                new ExcelCellChange { Row = 26, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 6) },
                                new ExcelCellChange { Row = 27, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 7) },
                                new ExcelCellChange { Row = 28, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 8) },
                                new ExcelCellChange { Row = 29, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 9) },
                                new ExcelCellChange { Row = 30, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 10) },
                                new ExcelCellChange { Row = 31, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 11) },
                                new ExcelCellChange { Row = 32, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 12) },
                                new ExcelCellChange { Row = 33, Column = 2, Value = s.FollowUps.Count(fu => fu.Speciality.SpecialityID == 13) },
                                new ExcelCellChange { Row = 8, Column = 7, Value = s.FollowUps.Count(fu => fu.FollowUpTakePart == 1) },
                                new ExcelCellChange { Row = 9, Column = 7, Value = s.FollowUps.Count(fu => fu.FollowUpTakePart == 2) },
                                new ExcelCellChange { Row = 10, Column = 7, Value = s.FollowUps.Count(fu => fu.FollowUpTakePart == 3) },
                                new ExcelCellChange { Row = 11, Column = 7, Value = s.FollowUps.Count(fu => fu.FollowUpTakePart == 4) },
                                new ExcelCellChange { Row = 12, Column = 7, Value = s.FollowUps.Count(fu => fu.FollowUpTakePart == 5) },
                                new ExcelCellChange { Row = 13, Column = 7, Value = s.FollowUps.Count(fu => fu.FollowUpTakePart == 6) },
                                new ExcelCellChange { Row = 17, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident1)??0 },
                                new ExcelCellChange { Row = 18, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident2)??0 },
                                new ExcelCellChange { Row = 19, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident3)??0 },
                                new ExcelCellChange { Row = 20, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident4)??0 },
                                new ExcelCellChange { Row = 21, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident5)??0 },
                                new ExcelCellChange { Row = 22, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident6)??0 },
                                new ExcelCellChange { Row = 23, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident7)??0 },
                                new ExcelCellChange { Row = 24, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident8)??0 },
                                new ExcelCellChange { Row = 25, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident9)??0 },
                                new ExcelCellChange { Row = 26, Column = 7, Value = s.FollowUps.Average(fu => fu.FollowUpConfident10)??0 }
                              });
                    sheets.Add(sheet);                    
                }

                #endregion

                // First sheet cells
                excelFistPageChanges.AddRange(new List<ExcelCellChange>
                              {
                                      new ExcelCellChange { Row = row, Column = 2, Value = ta.Country.CountryName },
                                      new ExcelCellChange { Row = row, Column = 3, Value = ta.TargetAreaCode },
                                      new ExcelCellChange { Row = row, Column = 5, Value = ta.Seminars.Sum(s => s.FollowUps.Count) },

                                      new ExcelCellChange { Row = row, Column = 7, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpAge))??0 },
                                      new ExcelCellChange { Row = row, Column = 8, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Gender.GenderName == "Male")) },
                                      new ExcelCellChange { Row = row, Column = 9, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Gender.GenderName == "Female")) },

                                      
                                      new ExcelCellChange { Row = row, Column = 11, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 1)) },
                                      new ExcelCellChange { Row = row, Column = 12, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 2)) },
                                      new ExcelCellChange { Row = row, Column = 13, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 3)) },
                                      new ExcelCellChange { Row = row, Column = 14, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 4)) },
                                      new ExcelCellChange { Row = row, Column = 15, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 5)) },
                                      new ExcelCellChange { Row = row, Column = 16, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 6)) },

                                      new ExcelCellChange { Row = row, Column = 18, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 1)) },
                                      new ExcelCellChange { Row = row, Column = 19, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 2)) },
                                      new ExcelCellChange { Row = row, Column = 20, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 3)) },
                                      new ExcelCellChange { Row = row, Column = 21, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 4)) },
                                      new ExcelCellChange { Row = row, Column = 22, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 5)) },
                                      new ExcelCellChange { Row = row, Column = 23, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 6)) },
                                      new ExcelCellChange { Row = row, Column = 24, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 7)) },
                                      new ExcelCellChange { Row = row, Column = 25, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 8)) },
                                      new ExcelCellChange { Row = row, Column = 26, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 9)) },
                                      new ExcelCellChange { Row = row, Column = 27, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 10)) },
                                      new ExcelCellChange { Row = row, Column = 28, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 11)) },
                                      new ExcelCellChange { Row = row, Column = 29, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 12)) },
                                      new ExcelCellChange { Row = row, Column = 30, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.Speciality.SpecialityID == 13)) },

                                      new ExcelCellChange { Row = row, Column = 32, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.FollowUpTakePart == 1)) },
                                      new ExcelCellChange { Row = row, Column = 33, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.FollowUpTakePart == 2)) },
                                      new ExcelCellChange { Row = row, Column = 34, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.FollowUpTakePart == 3)) },
                                      new ExcelCellChange { Row = row, Column = 35, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.FollowUpTakePart == 4)) },
                                      new ExcelCellChange { Row = row, Column = 36, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.FollowUpTakePart == 5)) },
                                      new ExcelCellChange { Row = row, Column = 37, Value = ta.Seminars.Sum(tar => tar.FollowUps.Count(fu => fu.FollowUpTakePart == 6)) },

                                      new ExcelCellChange { Row = row, Column = 39, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident1))??0 },
                                      new ExcelCellChange { Row = row, Column = 40, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident2))??0 },
                                      new ExcelCellChange { Row = row, Column = 41, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident3))??0 },
                                      new ExcelCellChange { Row = row, Column = 42, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident4))??0 },
                                      new ExcelCellChange { Row = row, Column = 43, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident5))??0 },
                                      new ExcelCellChange { Row = row, Column = 44, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident6))??0 },
                                      new ExcelCellChange { Row = row, Column = 45, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident7))??0 },
                                      new ExcelCellChange { Row = row, Column = 46, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident8))??0 },
                                      new ExcelCellChange { Row = row, Column = 47, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident9))??0 },
                                      new ExcelCellChange { Row = row, Column = 48, Value = ta.Seminars.Average(tar => tar.FollowUps.Average(fu => fu.FollowUpConfident10))??0 },

                              });

                row++;

            }

            excelFistPageChanges.AddRange(new List<ExcelCellChange>
                              {
                                      new ExcelCellChange { Row = 5, Column = 5, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count)) },
                                      new ExcelCellChange { Row = 5, Column = 7, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpAge)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 8, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Gender.GenderName == "Male"))) },
                                      new ExcelCellChange { Row = 5, Column = 9, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Gender.GenderName == "Female"))) },
                                      new ExcelCellChange { Row = 5, Column = 11, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 1))) },
                                      new ExcelCellChange { Row = 5, Column = 12, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 2))) },
                                      new ExcelCellChange { Row = 5, Column = 13, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 3))) },
                                      new ExcelCellChange { Row = 5, Column = 14, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 4))) },
                                      new ExcelCellChange { Row = 5, Column = 15, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 5))) },
                                      new ExcelCellChange { Row = 5, Column = 16, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.ProfessionalBackground.ProfessionalBackgroundID == 6))) },
                                      new ExcelCellChange { Row = 5, Column = 18, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 1))) },
                                      new ExcelCellChange { Row = 5, Column = 19, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 2))) },
                                      new ExcelCellChange { Row = 5, Column = 20, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 3))) },
                                      new ExcelCellChange { Row = 5, Column = 21, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 4))) },
                                      new ExcelCellChange { Row = 5, Column = 22, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 5))) },
                                      new ExcelCellChange { Row = 5, Column = 23, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 6))) },
                                      new ExcelCellChange { Row = 5, Column = 24, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 7))) },
                                      new ExcelCellChange { Row = 5, Column = 25, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 8))) },
                                      new ExcelCellChange { Row = 5, Column = 26, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 9))) },
                                      new ExcelCellChange { Row = 5, Column = 27, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 10))) },
                                      new ExcelCellChange { Row = 5, Column = 28, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 11))) },
                                      new ExcelCellChange { Row = 5, Column = 29, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 12))) },
                                      new ExcelCellChange { Row = 5, Column = 30, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.Speciality.SpecialityID == 13))) },
                                      new ExcelCellChange { Row = 5, Column = 32, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.FollowUpTakePart.Value == 1))) },
                                      new ExcelCellChange { Row = 5, Column = 33, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.FollowUpTakePart.Value == 2))) },
                                      new ExcelCellChange { Row = 5, Column = 34, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.FollowUpTakePart.Value == 3))) },
                                      new ExcelCellChange { Row = 5, Column = 35, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.FollowUpTakePart.Value == 4))) },
                                      new ExcelCellChange { Row = 5, Column = 36, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.FollowUpTakePart.Value == 5))) },
                                      new ExcelCellChange { Row = 5, Column = 37, Value = programViewModel.Program.TargetAreas.Sum(p => p.Seminars.Sum(ta => ta.FollowUps.Count(fu => fu.FollowUpTakePart.Value == 6))) },
                                      new ExcelCellChange { Row = 5, Column = 39, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident1)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 40, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident2)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 41, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident3)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 42, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident4)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 43, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident5)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 44, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident6)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 45, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident7)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 46, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident8)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 47, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident9)))??0 },
                                      new ExcelCellChange { Row = 5, Column = 48, Value = programViewModel.Program.TargetAreas.Average(p => p.Seminars.Average(ta => ta.FollowUps.Average(fu => fu.FollowUpConfident10)))??0 },
                             });

            ExcelSheetInformation firstSheet = new ExcelSheetInformation
            {
                Name = "EODs Follow Up SUMMARY",
                Changes = excelFistPageChanges
            };

            sheets.Add(firstSheet);

            string temporaryFileName = Server.MapPath(@"~/temp/" + Guid.NewGuid() + ".xlsx");

            if (log.IsDebugEnabled) log.DebugFormat("Leaving Program with id {0} ta...", id);

            return this.Excel("EOD_followup_report.xlsx", temporaryFileName, Server.MapPath(@"~/templates/EOD_Seminars_Follow-up.xlsx"), "EODs Follow Up SUMMARY", sheets.AsQueryable());
        }


        [Authorize(Roles = "Administrator")]
        public ActionResult Fix()
        {
            Repository.FixSurveyResults();
            return RedirectToAction("Index", "Home");
        }


    }
}
